#include "datasumwidget.h"
#include "ui_datasumwidget.h"

DataSumWidget::DataSumWidget(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::DataSumWidget)
{
    ui->setupUi(this);

    setWindowFlags(windowFlags()&~Qt::WindowMaximizeButtonHint);
    setFixedSize(width(),height());

    //初始化tableWidget
    initTableWidget();


}

DataSumWidget::~DataSumWidget()
{
    delete ui;
}

//初始化表格控件
void DataSumWidget::initTableWidget()
{
    ui->tableWidget->setRowCount(200);
    ui->tableWidget->setColumnCount(10);

    //自定义表格控件的字体大小
    ui->tableWidget->setFont(QFont("宋体",9));

    //设置表头
    QStringList strList;
    strList << "商品编号" << "商品名称" << "商品数量" << "商品单价" << "商品总价" << "供应商家" << "负责人" << "入库事件" << "出库时间"
            << "备注";
    ui->tableWidget->setHorizontalHeaderLabels(strList);

}

//点击商品汇总按钮
void DataSumWidget::on_pushButton_comSum_clicked()
{
    initTableWidget();
    QSqlQuery query;
    QString sql = "select * from commodity_data";
    // 保留表头，清空单元格内所有内容
    ui->tableWidget->clearContents();

    query.exec(sql);
    int i = 0;
    while(query.next())
    {
        QString stockId = query.value(0).toString();
        QString stockName = query.value(1).toString();
        QString stockAmount = query.value(2).toString();
        QString stockUnitPrice = query.value(3).toString();
        double stockSum = stockAmount.toDouble() * stockUnitPrice.toDouble();
        qDebug() << "stockSum=" << stockSum;
        QString supplier = query.value(4).toString();
        QString director = query.value(5).toString();
        QString storeTime = query.value(6).toString();
        QString deliveryTime = query.value(7).toString();
        QString remarks = query.value(8).toString();

        //将对应的字符串设置到widgetList中
        ui->tableWidget->setItem(i,0,new QTableWidgetItem(stockId));
        ui->tableWidget->setItem(i,1,new QTableWidgetItem(stockName));
        ui->tableWidget->setItem(i,2,new QTableWidgetItem(stockAmount));
        ui->tableWidget->setItem(i,3,new QTableWidgetItem(stockUnitPrice));
        ui->tableWidget->setItem(i,4,new QTableWidgetItem(QString::number(stockSum)));
        ui->tableWidget->setItem(i,5,new QTableWidgetItem(supplier));
        ui->tableWidget->setItem(i,6,new QTableWidgetItem(director));
        ui->tableWidget->setItem(i,7,new QTableWidgetItem(storeTime));
        ui->tableWidget->setItem(i,8,new QTableWidgetItem(deliveryTime));
        ui->tableWidget->setItem(i,9,new QTableWidgetItem(remarks));
        i++;
    }
}

//点击入库汇总按钮
void DataSumWidget::on_pushButton_restoreSum_clicked()
{
    //重构tableWidget
    ui->tableWidget->setColumnCount(5);
    ui->tableWidget->setRowCount(200);
    QStringList strList;
    strList << "商品编号" << "商品名称" << "入库数量" << "商品总量" << "入库日期";
    ui->tableWidget->setHorizontalHeaderLabels(strList);

    QSqlQuery query;
    QString sql = "select * from commodity_store";
    // 保留表头，清空单元格内所有内容
    ui->tableWidget->clearContents();

    query.exec(sql);
    int i = 0;
    while(query.next())
    {
        QString stockId = query.value(0).toString();
        QString stockName = query.value(1).toString();
        QString stockStoreAmount = query.value(2).toString();
        QString amount = query.value(3).toString();
        QString storeTime = query.value(4).toString();

        //将对应的字符串设置到widgetList中
        ui->tableWidget->setItem(i,0,new QTableWidgetItem(stockId));
        ui->tableWidget->setItem(i,1,new QTableWidgetItem(stockName));
        ui->tableWidget->setItem(i,2,new QTableWidgetItem(stockStoreAmount));
        ui->tableWidget->setItem(i,3,new QTableWidgetItem(amount));
        ui->tableWidget->setItem(i,4,new QTableWidgetItem((storeTime)));

        i++;
    }
}

void DataSumWidget::on_pushButton_deliverySum_clicked()
{
    //重构tableWidget
    ui->tableWidget->setColumnCount(5);
    ui->tableWidget->setRowCount(200);
    QStringList strList;
    strList << "商品编号" << "商品名称" << "出库数量" << "商品总量" << "出库日期";
    ui->tableWidget->setHorizontalHeaderLabels(strList);

    QSqlQuery query;
    QString sql = "select * from commodity_delivery";
    // 保留表头，清空单元格内所有内容
    ui->tableWidget->clearContents();

    query.exec(sql);
    int i = 0;
    while(query.next())
    {
        QString stockId = query.value(0).toString();
        QString stockName = query.value(1).toString();
        QString stockStoreAmount = query.value(2).toString();
        QString amount = query.value(3).toString();
        QString deliveryTime = query.value(4).toString();

        //将对应的字符串设置到widgetList中
        ui->tableWidget->setItem(i,0,new QTableWidgetItem(stockId));
        ui->tableWidget->setItem(i,1,new QTableWidgetItem(stockName));
        ui->tableWidget->setItem(i,2,new QTableWidgetItem(stockStoreAmount));
        ui->tableWidget->setItem(i,3,new QTableWidgetItem(amount));
        ui->tableWidget->setItem(i,4,new QTableWidgetItem((deliveryTime)));

        i++;
    }
}

void DataSumWidget::on_pushButton_outputData_clicked()
{
    QTableWidget *table = ui->tableWidget;
    int colCount = table->columnCount();

    //1.检查有效数据
    bool hasData = false;
    for (int row = 0; row < table->rowCount(); row++) {
        if (table->item(row, 0) && !table->item(row, 0)->text().isEmpty()) {
            hasData = true;
            break;
        }
    }
    if (!hasData) {
        QMessageBox::warning(this, "提示", "没有有效数据可导出！");
        return;
    }

    //2.初始化 Excel 对象
    QAxObject *excel = new QAxObject;
    //判断系统是否安装Office的运行时环境
    if (!excel->setControl("Excel.Application")) {
        QMessageBox::critical(this, "错误", "无法创建 Excel 对象，请确认已安装 Microsoft Excel。");
        delete excel;
        return;
    }
    //excel->setProperty("Visible", true); // 临时显示窗口（调试）

    //3.工作簿与工作表的创建
    QAxObject *workbooks = excel->querySubObject("Workbooks");
    QAxObject *workbook = workbooks->querySubObject("Add");
    QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1);

    //4.写入表头
    for (int col = 0; col < colCount; col++) {
        QTableWidgetItem *header = table->horizontalHeaderItem(col);
        QString title = header ? header->text() : "";
        QAxObject *cell = worksheet->querySubObject("Cells(int,int)", 1, col + 1);//定位一个单元格
        cell->dynamicCall("SetValue(const QString&)", title);//往单元格中加入内容
        delete cell;
    }

    //5.写入数据
    int excelRow = 2;
    for (int row = 0; row < table->rowCount(); row++) {
        if (!table->item(row, 0) || table->item(row, 0)->text().isEmpty()) {
            continue;
        }
        for (int col = 0; col < colCount; col++) {
            QTableWidgetItem *item = table->item(row, col);
            QString value = item ? item->text() : "";
            QAxObject *cell = worksheet->querySubObject("Cells(int,int)", excelRow, col + 1);
            cell->dynamicCall("SetValue(const QString&)", value);
            delete cell;
        }
        excelRow++;
    }

    //6.调整列宽
    QAxObject *usedRange = worksheet->querySubObject("UsedRange");
    usedRange->querySubObject("Columns")->dynamicCall("AutoFit()");
    delete usedRange;

    //7.保存文件处理
    QString fileName = "数据汇总_" + QDateTime::currentDateTime().toString("yyyyMMdd") + ".xlsx";
    QString filePath = QFileDialog::getSaveFileName(
                this,
                "保存文件",
                fileName,
                "Excel文件 (*.xlsx)"
                );
    if (!filePath.isEmpty()) {
        if (!filePath.endsWith(".xlsx", Qt::CaseInsensitive)) {
            filePath += ".xlsx";
        }
        //将文件路径中的目录分隔符转换为当前操作系统的标准格式
        QString nativePath = QDir::toNativeSeparators(filePath);
        workbook->dynamicCall("SaveAs(const QString&)", nativePath);
        QMessageBox::information(this, "成功", QString("文件已保存至：\n%1").arg(nativePath));
    }

    //8.释放资源与进程清理
    workbook->dynamicCall("Close()");
    excel->dynamicCall("Quit()");
    delete worksheet;
    delete workbook;
    delete workbooks;
    delete excel;

    //9.强制终止 Excel 进程
    QProcess::execute("taskkill /F /IM EXCEL.EXE");
}
